package milkproduction.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.tomcat.dbcp.dbcp.BasicDataSource;

import milkproduction.to.OrderTO;
import milkproduction.to.ReleaseTO;
import miracom.one.MilkProduction.util.DatabaseUtility;

public class ReleaseDAO {

	private static ReleaseDAO instance = new ReleaseDAO();
	private DatabaseUtility dbUtil;
	private ReleaseDAO(){
		dbUtil = DatabaseUtility.getInstance();
	}
	public static ReleaseDAO getInstance(){
		return instance;
	}


	// 생산된 총 완제품의 갯수 
	public int selectFinishedProductCount() throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int totalProduction = 0;
		String sql = "select count(FINISHED_PRODUCT_NO) from FINISHED_PRODUCT";
		try{
			BasicDataSource dataSource = dbUtil.getDataSource();
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
				totalProduction = rset.getInt(1);
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return totalProduction;
	}
	// 생산된 오늘의 완제품 갯수 
	public int selectFinishedProductCountByTodayDate() throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int todayProductionCnt = 0;
		String sql = "select count(FINISHED_PRODUCT) from FINISHED_PRODUCT where TO_CHAR(PRODUCT_DAY,'YYYYMMDD') = TO_CHAR(sysdate, 'YYYYMMDD')";
		try{
			BasicDataSource dataSource = dbUtil.getDataSource();
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
				todayProductionCnt = rset.getInt(1);
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return todayProductionCnt;
	}
	
	// 오늘날짜의 주문 조회하기
	public OrderTO selectOrderByTodayDate() throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		OrderTO oto = null;
		String sql = "select ORDER_NO,TO_CHAR(ORDER_DATE, 'YYYYMMDD'),ORDERER, QUANTITY, DELIVERY_STATE from orders where TO_CHAR(order_date, 'YYYYMMDD') = TO_CHAR(sysdate, 'YYYYMMDD')";
		try{
			BasicDataSource dataSource = dbUtil.getDataSource();
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			if(rset.next()){
				if(rset.getString(5).equals(true)){
				oto = new OrderTO(rset.getInt(1),
					 	rset.getString(2),
					 	rset.getString(3),
					 	rset.getInt(4),
					 	true);
				}else{
					oto = new OrderTO(rset.getInt(1),
						 	rset.getString(2),
						 	rset.getString(3),
						 	rset.getInt(4),
						 	false);
				}
			}
		}finally{
			dbUtil.close(rset, pstmt, conn);
		}
		return oto;
	}
	
	// 출고 정보 넣기
	public int insertRelease(int orderNo) throws SQLException{
		int insertCnt = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "insert into RELEASE values (RELEASE_NO_SEQ.nextval,sysdate,?)";
		try{
			BasicDataSource dataSource = dbUtil.getDataSource();
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, orderNo);
			insertCnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(pstmt, conn);
		}
		return insertCnt;
	}
	
	// 완제품에 출고정보 update 
	public int updateFinishProductionReleaseNo(int releaseNo)throws SQLException{
		int updateReleaseNOCnt = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update FINISHED_PRODUCT set RELEASE_NO = ? where TO_CHAR(PRODUCT_DAY,'YYYYMMDD') = TO_CHAR(sysdate, 'YYYYMMDD') ";
		try{
			BasicDataSource dataSource = dbUtil.getDataSource();
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, releaseNo);
			updateReleaseNOCnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(pstmt, conn);
		}
		return updateReleaseNOCnt;
	}
	
	// orders에 납품상태 update 
		public int updateDeliveryState(int orderNo)throws SQLException{
			int updateDeliverycnt = 0;
			Connection conn = null;
			PreparedStatement pstmt = null;
			String sql = "update ORDERS set DELIVERY_STATE = 'true' where ORDER_NO = ?";
			try{
				BasicDataSource dataSource = dbUtil.getDataSource();
				conn = dataSource.getConnection();
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, orderNo);
				updateDeliverycnt = pstmt.executeUpdate();
			}finally{
				dbUtil.close(pstmt, conn);
			}
			return updateDeliverycnt;
		}
		
		// 현재 출고번호를 조회하는 메소드
		public int selectReleaseNoCurrval() throws SQLException{
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rset = null;
			int releaseNo = 0;
			String sql = "select last_number FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'RELEASE_NO_SEQ'";
			try{
				BasicDataSource dataSource = dbUtil.getDataSource();
				conn = dataSource.getConnection();
				pstmt = conn.prepareStatement(sql);
				rset = pstmt.executeQuery();
				if(rset.next()){
					releaseNo = rset.getInt(1);
				}
			}finally{
				dbUtil.close(rset, pstmt, conn);
			}
			return releaseNo;
		}
		

}
